#Boa:Dialog:Dialog1

import wx
import wx.grid
import wx.calendar
import consultasBD
import utiles

def create(parent):
    return Dialog1(parent)

[wxID_DIALOG1, wxID_DIALOG1BUTTON1, wxID_DIALOG1CHECKBOX1, 
 wxID_DIALOG1DATEPICKERCTRL1, wxID_DIALOG1DATEPICKERCTRL2, 
 wxID_DIALOG1DETALLES, wxID_DIALOG1GRID1, wxID_DIALOG1STATICTEXT1, 
 wxID_DIALOG1STATICTEXT2, 
] = [wx.NewId() for _init_ctrls in range(9)]

class Dialog1(wx.Dialog):
    def _init_ctrls(self, prnt):
        # generated method, don't edit
        wx.Dialog.__init__(self, id=wxID_DIALOG1, name='', parent=prnt,
              pos=wx.Point(223, 171), size=wx.Size(505, 460),
              style=wx.DEFAULT_DIALOG_STYLE, title='Listado de Saldos...')
        self.SetClientSize(wx.Size(489, 424))

        self.staticText1 = wx.StaticText(id=wxID_DIALOG1STATICTEXT1,
              label='Desde:', name='staticText1', parent=self, pos=wx.Point(16,
              16), size=wx.Size(35, 13), style=0)

        self.staticText2 = wx.StaticText(id=wxID_DIALOG1STATICTEXT2,
              label='Hasta:', name='staticText2', parent=self, pos=wx.Point(176,
              16), size=wx.Size(33, 13), style=0)

        self.datePickerCtrl1 = wx.DatePickerCtrl(id=wxID_DIALOG1DATEPICKERCTRL1,
              name='datePickerCtrl1', parent=self, pos=wx.Point(64, 16),
              size=wx.Size(96, 21), style=wx.DP_DROPDOWN)
        self.datePickerCtrl1.SetLabel('13/06/2008')
        self.datePickerCtrl1.SetValue(wx.DateTimeFromDMY(13, 5, 2008, 0, 0, 0))
        self.datePickerCtrl1.Bind(wx.EVT_DATE_CHANGED,
              self.OnDatePickerCtrl1DateChanged,
              id=wxID_DIALOG1DATEPICKERCTRL1)

        self.datePickerCtrl2 = wx.DatePickerCtrl(id=wxID_DIALOG1DATEPICKERCTRL2,
              name='datePickerCtrl2', parent=self, pos=wx.Point(224, 16),
              size=wx.Size(96, 21), style=wx.DP_DROPDOWN)
        self.datePickerCtrl2.Bind(wx.EVT_DATE_CHANGED,
              self.OnDatePickerCtrl2DateChanged,
              id=wxID_DIALOG1DATEPICKERCTRL2)

        self.grid1 = wx.grid.Grid(id=wxID_DIALOG1GRID1, name='grid1',
              parent=self, pos=wx.Point(40, 96), size=wx.Size(360, 272),
              style=0)

        self.checkBox1 = wx.CheckBox(id=wxID_DIALOG1CHECKBOX1,
              label='Por Email', name='checkBox1', parent=self,
              pos=wx.Point(344, 16), size=wx.Size(128, 13), style=0)
        self.checkBox1.SetValue(False)

        self.button1 = wx.Button(id=wxID_DIALOG1BUTTON1, label='Consultar',
              name='button1', parent=self, pos=wx.Point(192, 48),
              size=wx.Size(88, 31), style=0)
        self.button1.Bind(wx.EVT_BUTTON, self.OnButton1Button,
              id=wxID_DIALOG1BUTTON1)

        self.detalles = wx.Button(id=wxID_DIALOG1DETALLES, label='Detalles -->',
              name='detalles', parent=self, pos=wx.Point(408, 88),
              size=wx.Size(75, 23), style=0)
        self.detalles.Bind(wx.EVT_BUTTON, self.OnDetallesButton,
              id=wxID_DIALOG1DETALLES)

    def __init__(self, parent):
        self._init_ctrls(parent)
        self.padre = parent
        
        self.grid1.CreateGrid(4,4)
        
        self.grid1.SetColLabelValue(0,"     VALORES DE ...    ")
        self.grid1.SetColLabelValue(1,"     EN PESOS .....    ")
        
        if self.padre.loginRol == "cajera":
            self.checkBox1.Enable(True)
        else: 
            self.checkBox1.Enable(False)
        
        self.grid1.AutoSizeColumn(0,True)
        self.grid1.AutoSizeColumn(1,True)

    def OnDatePickerCtrl1DateChanged(self, event):
        print self.datePickerCtrl1.GetValue()
        print self.datePickerCtrl2.GetValue()
        
    def OnDatePickerCtrl2DateChanged(self, event):
        event.Skip()

    def OnButton1Button(self, event):
        
        valor = self.checkBox1.Value
        if valor:
            formaticket=2
        else:
            formaticket=1
        
        
        desde = str(self.datePickerCtrl1.GetValue())[:8]
        hasta = str(self.datePickerCtrl2.GetValue())[:8]
        
        print desde
        print hasta
        
        desde = self.formatear_fecha_date2(desde)
        hasta = self.formatear_fecha_date2(hasta)
        SQL = "SELECT sum(montocuota) - sum(montopagado) as TOTAL FROM vtas_ctasctes vtc, ventas v WHERE montocuota>0 AND montocuota!=0 AND v.idventa=vtc.idventa AND v.idformaticket='%s' and v.fecha between '%s' AND '%s'" % (formaticket,desde,hasta)
        print SQL
        result = consultasBD.ordenCrudaSinCommit(SQL)
        for r in result:
            self.grid1.SetCellValue(0,0,"SALDOS EN"+str(formaticket))
            self.grid1.SetCellValue(0,1,str(r["TOTAL"]))
            
            
        
    def formatear_fecha_date2(self,fecha):
        aux = str(fecha).split(" ")[0].split("/")
        return "20%s-%s-%s" % (aux[2], aux[0], aux[1]) 

    def OnDetallesButton(self, event):
        self.saldos = []
        self.saldos.append(["IDCLIENTE","NOMBRE","TELEFONO","SALDO TOTAL","NO VENCIDO","MENOS 30","MENOS 60","MENOS 90","90 o MAS"])
        SQL = "SELECT c.idcliente, c.nombre,c.telefono, clientes_con_saldo.saldo as SALDO_TOTAL, saldo_no_vencido.saldo as NO_VENCIDO, saldo_menos_30.saldo as MENOS_30, saldo_menos_60.saldo as MENOS_60, saldo_menos_90.saldo as MENOS_90, saldo_mas_90.saldo as M90 FROM (SELECT IDCLIENTE, SUM( MONTOCUOTA ) - SUM( MONTOPAGADO ) AS saldo FROM vtas_ctasctes WHERE MONTOCUOTA != MONTOPAGADO GROUP BY IDCLIENTE) clientes_con_saldo LEFT JOIN(SELECT IDCLIENTE, SUM( MONTOCUOTA ) , SUM( MONTOPAGADO ) , SUM(MONTOCUOTA ) - SUM( MONTOPAGADO ) AS saldo FROM vtas_ctasctes WHERE MONTOCUOTA != MONTOPAGADO AND DATEDIFF( CURDATE( ) , vencimiento ) <= 0 GROUP BY IDCLIENTE) saldo_no_vencido ON(clientes_con_saldo.IDCLIENTE = saldo_no_vencido.IDCLIENTE) LEFT JOIN(    SELECT IDCLIENTE, SUM( MONTOCUOTA ) , SUM( MONTOPAGADO ) , SUM(MONTOCUOTA ) - SUM( MONTOPAGADO ) AS saldo FROM vtas_ctasctes  WHERE MONTOCUOTA != MONTOPAGADO AND DATEDIFF( CURDATE( ) , vencimiento ) BETWEEN 1 AND 29 GROUP BY IDCLIENTE) saldo_menos_30 ON(clientes_con_saldo.IDCLIENTE = saldo_menos_30.IDCLIENTE) LEFT JOIN(SELECT IDCLIENTE, SUM( MONTOCUOTA ) , SUM( MONTOPAGADO ) , SUM(MONTOCUOTA ) - SUM( MONTOPAGADO ) AS saldo FROM vtas_ctasctes WHERE MONTOCUOTA != MONTOPAGADO AND DATEDIFF( CURDATE( ) , vencimiento ) BETWEEN 30 AND 59 GROUP BY IDCLIENTE) saldo_menos_60 ON(clientes_con_saldo.IDCLIENTE = saldo_menos_60.IDCLIENTE) LEFT JOIN(SELECT IDCLIENTE, SUM( MONTOCUOTA ) , SUM( MONTOPAGADO ) , SUM( MONTOCUOTA ) - SUM( MONTOPAGADO ) AS saldo FROM vtas_ctasctes WHERE MONTOCUOTA != MONTOPAGADO AND DATEDIFF( CURDATE( ) , vencimiento ) BETWEEN 60 AND 89 GROUP BY IDCLIENTE) saldo_menos_90 ON(clientes_con_saldo.IDCLIENTE = saldo_menos_90.IDCLIENTE) LEFT JOIN(SELECT IDCLIENTE, SUM( MONTOCUOTA ) , SUM( MONTOPAGADO ) , SUM(MONTOCUOTA ) - SUM( MONTOPAGADO ) AS saldo FROM vtas_ctasctes WHERE MONTOCUOTA != MONTOPAGADO AND DATEDIFF( CURDATE( ) , vencimiento ) > 90 GROUP BY IDCLIENTE) saldo_mas_90 ON(clientes_con_saldo.IDCLIENTE = saldo_mas_90.IDCLIENTE) JOIN cliente c ON (clientes_con_saldo.IDCLIENTE = c.idcliente)  ORDER BY c.nombre ASC"
        result = consultasBD.ordenCrudaSinCommit(SQL)
        for r in result:
            
            
            telefono = r["telefono"]
            SALDO_TOTAL = r["SALDO_TOTAL"]
            NO_VENCIDO = r["NO_VENCIDO"]
            MENOS_30 = r["MENOS_30"]
            MENOS_60 = r["MENOS_60"]
            MENOS_90 = r["MENOS_90"]
            M90 = r["M90"]
            
            
            if (r["telefono"])==None: telefono=""
            if (r["SALDO_TOTAL"])==None: saldototal=""
            if (r["NO_VENCIDO"])==None: NO_VENCIDO=""
            if (r["MENOS_30"])==None: MENOS_30=""
            if (r["MENOS_60"])==None: MENOS_60=""
            if (r["MENOS_90"])==None: MENOS_90=""
            if (r["M90"])==None: M90=""
            
            
            print r["idcliente"]
            print r["nombre"]
            print SALDO_TOTAL
            print NO_VENCIDO
            print MENOS_30
            print MENOS_60
            print MENOS_90
            print M90
            
            if r["nombre"]!="Venta": 
                variable = ["" + str(r["idcliente"])+"",""+str(r["nombre"]+""),str(telefono), str(SALDO_TOTAL), str(NO_VENCIDO), str(MENOS_30), str(MENOS_60), str(MENOS_90), str(M90)]
                print variable
                self.saldos.append(variable)
                
        print self.saldos
        utiles.reporte_html(self,self.saldos, "","0")
